package org.huangrui.spark.scala.sql

import org.apache.spark.sql.SparkSession

/**
 * @Author hr
 * @Create 2024-10-20 22:19 
 */
object SparkSQL09_Source_Req {
  def main(args: Array[String]): Unit = {
    // TODO 在编码前，设定Hadoop的访问用户
    System.setProperty("HADOOP_USER_NAME", "huangrui")
    // TODO 启用Hive的支持
    val spark = SparkSession.builder.enableHiveSupport.appName("SparkSQL08_Source_Hive").master("local[2]").getOrCreate
//    spark.sql("create database db_spark")
    spark.sql("show databases").show()
    spark.sql("use db_spark")
        spark.sql("drop table db_spark.user_visit_action")
        spark.sql("drop table db_spark.product_info")
        spark.sql("drop table db_spark.city_info")

    spark.sql(
      """
        |CREATE TABLE IF NOT EXISTS `user_visit_action`(
        |  `date` string,
        |  `user_id` bigint,
        |  `session_id` string,
        |  `page_id` bigint,
        |  `action_time` string,
        |  `search_keyword` string,
        |  `click_category_id` bigint,
        |  `click_product_id` bigint, --点击商品id，没有商品用-1表示。
        |  `order_category_ids` string,
        |  `order_product_ids` string,
        |  `pay_category_ids` string,
        |  `pay_product_ids` string,
        |  `city_id` bigint --城市id
        |)
        |row format delimited fields terminated by '\t';
        |""".stripMargin)

        spark.sql(
          """
            |CREATE TABLE IF NOT EXISTS `city_info`(
            |  `city_id` bigint, --城市id
            |  `city_name` string, --城市名称
            |  `area` string --区域名称
            |)
            |row format delimited fields terminated by '\t';
            |""".stripMargin)

      spark.sql(
        """
          |CREATE TABLE IF NOT EXISTS `product_info`(
          |  `product_id` bigint, -- 商品id
          |  `product_name` string, --商品名称
          |  `extend_info` string
          |)
          |row format delimited fields terminated by '\t';
          |""".stripMargin)

    spark.sql("load data local inpath 'datas/user_visit_action.txt' into table user_visit_action;")
    spark.sql("load data local inpath 'datas/city_info.txt' into table city_info;")
    spark.sql("load data local inpath 'datas/product_info.txt' into table product_info;")

    spark.sql("select * from city_info").show()

    spark.stop()
  }
}
